import 'package:floor/floor.dart';
import 'package:share_fit/app/dao/dao_mixin.dart';
import 'package:share_fit/app/models/home/heart_rate_model.dart';

@dao
abstract class HeartRateDao with DaoMixin<HeartRateModel> {
  @override
  @Query('SELECT * FROM heart_rate WHERE _id = :id')
  Stream<HeartRateModel?> findById(int id);

  @override
  @Query('SELECT * FROM heart_rate')
  Future<List<HeartRateModel>> findAll();

  /// 根据日期条件降序获取最新的一条心率记录
  @Query('SELECT * FROM heart_rate where address = :address and calendar = :calendar order by calendar_time desc limit 1')
  Future<HeartRateModel?> findLatestOne(String address, String calendar);

  /// 根据日期条件获取24小时内平均心率记录
  @Query("SELECT "
      "CAST(AVG(heart_rate_max) AS INT) as heart_rate_max, "
      "CAST(AVG(heart_rate_min) AS INT) as heart_rate_min, "
      "CAST(AVG(heart_rate_avg) AS INT) as heart_rate_avg, "
      "STRFTIME('%H', calendar_time) as calendar "
      "FROM heart_rate WHERE address = :address and calendar = :calendar GROUP BY strftime('%H', calendar_time) order by calendar")
  Future<List<HeartRateModel>> groupByHour(String address, String calendar);

  /// 根据日期条件获取24小时内所有的心率记录
  @Query("SELECT "
      "heart_rate_max, "
      "heart_rate_min, "
      "heart_rate_avg, "
      "calendar_time  "
      "FROM heart_rate WHERE address = :address and calendar = :calendar GROUP BY calendar_time order by calendar_time")
  Future<List<HeartRateModel>> findAllBy(String address, String calendar);

  /// 获取指定时间范围内按日期分组后的最大、最小、平均心率记录
  @Query("SELECT "
      "CAST(max(heart_rate_max) AS INT) as heart_rate_max, "
      "CAST(min(heart_rate_min) AS INT) as heart_rate_min, "
      "CAST(AVG(heart_rate_avg) AS INT) as heart_rate_avg, "
      "calendar "
      "FROM heart_rate WHERE address = :address and calendar >= :dateStart and calendar <= :dateEnd GROUP BY calendar order by calendar")
  Future<List<HeartRateModel>> findInRange(String address, String dateStart, String dateEnd);

  /// 获取指定时间范围内平均心率记录
  @Query("SELECT "
      "max(heart_rate_avg) as heart_rate_max, "
      "min(heart_rate_avg) as heart_rate_min, "
      "CAST(AVG(heart_rate_avg) AS INT) as heart_rate_avg "
      "FROM heart_rate WHERE address = :address and calendar >= :dateStart and calendar <= :dateEnd")
  Future<HeartRateModel?> findAvg(String address, String dateStart, String dateEnd);
  
  /// 查找指定月份中有心率数据的日期
  @Query("select distinct calendar from heart_rate "
      "where address = :address and STRFTIME('%Y', calendar_time) >= :minYear and STRFTIME('%Y', calendar_time) <= :maxYear")
  Future<List<String>> findHasRecordDays(String address, String minYear, String maxYear);

  @Query('select * from heart_rate where address = :address and calendar >= :calenderStart and calendar <= :calendarEnd')
  Future<List<HeartRateModel>> findForReport(String address, String calenderStart, String calendarEnd);

  @override
  @Query('SELECT * FROM heart_rate')
  Stream<List<HeartRateModel>> findAllAsStream();

  @override
  @Query('delete FROM heart_rate')
  Future<void> deleteAll();
  
  @Query('SELECT * FROM heart_rate WHERE date_created >= :startTime and date_created < :endTime')
  Future<List<HeartRateModel>> findBy(int startTime, int endTime);

  @Query('delete FROM heart_rate where address = :address')
  @override
  Future<void> deleteAllByAddress(String address);
}